lyfz_repos/铂金版源码/DBFile/SQL/2015-08-13[收集]/2015-08-15.sql


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_OrdersWageDigital')
BEGIN
	DROP VIEW [dbo].Vw_OrdersWageDigital
END 
GO
create View Vw_OrdersWageDigital
as
select 
tb_ErpOrderDigital.Id
, Ordv_Number
,Ordv_ViceNumber
,Ordv_DigitalNumber
,Ord_Number
,Ord_Type
,Ord_Class
,Ord_PhotographyCategory                     
,Ord_SeriesName                    
,Ord_SeriesPrice  
,(case when Ord_Type = '1' then (select Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber) else (select top 1 Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_Number = Ord_Number) end) as 拍摄名称
,(select Cus_Name from tempTB_AggregationCustomer where Ord_Number=GP_OrderNumber) as 客户名称
,(select Cus_Telephone from tempTB_AggregationCustomer where Ord_Number=GP_OrderNumber) as 客户电话
,Ordv_ClothingName  as  礼服师ID
,dbo.fn_CheckUserIDGetUserName(Ordv_ClothingName) as  礼服师
,Ordv_ClothingStatus as  礼服师状态 
,Ordv_ClothingTime as  选衣时间
, Ordv_FilmSelectionName as  选片师ID
, dbo.fn_CheckUserIDGetUserName(Ordv_FilmSelectionName)  as  选片师
, Ordv_FilmSelectionStatus  as  选片状态 
, Ordv_FilmSelectionTime  as  选片时间
, Ordv_EarlyRepairName  as  初修师ID
, dbo.fn_CheckUserIDGetUserName(Ordv_EarlyRepairName)  as  初修师
, Ordv_EarlyRepairStatus as  初修状态 
, Ordv_EarlyRepairTime  as  初修时间
, Ordv_RefinementName  as  精修师ID
, dbo.fn_CheckUserIDGetUserName(Ordv_RefinementName)as  精修师
, Ordv_RefinementStatus as  精修状态 
, Ordv_RefinementTime  as  精修时间
,Ordv_LookDesignName  as  看设计师ID
,dbo.fn_CheckUserIDGetUserName(Ordv_LookDesignName)  as  看设计师
,Ordv_LookDesignStatus as  看设计状态 
,Ordv_LookDesignTime  as  看设计时间
,Ordv_DesignerName as  设计师ID
,dbo.fn_CheckUserIDGetUserName(Ordv_DesignerName)  as  设计师
,Ordv_DesignerStatus  as  设计状态 
, Ordv_DesignerTime  as  设计时间
,(case when (select Count(*) from (select [OPlist_PickupStatus] from [tb_ErpOrderProductList] where [OPlist_ViceNumber]=Ordv_ViceNumber and OPlist_Type = '2' and [OPlist_PickupStatus] = '0') as ta)>0  then '未取' else 'OK' end) AS 取件状态
,(select top 1 OPlist_PickupTime from tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber and OPlist_Type = '2' and OPlist_PickupTime is not null order by OPlist_PickupTime) AS 取件日期
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select count(id) as id from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number and (Ordpg_PhotographyStatus=0 or Ordpg_PhotographyStatus=2))
   when 1 then (select count(id) as id from tb_ErpOrdersPhotography where  Ordv_ViceNumber=Ordpg_ViceNumber and (Ordpg_PhotographyStatus=0 or Ordpg_PhotographyStatus=2))
   when 2 then (select count(id) as id from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number and (Ordpg_PhotographyStatus=0 or Ordpg_PhotographyStatus=2))
   else '' end) as 未拍个数
from tb_ErpOrderDigital
left join tb_ErpOrder on tb_ErpOrderDigital.Ordv_Number=tb_ErpOrder.Ord_Number 



GO